﻿using System;
using System.Collections.Generic;
using System.Text;
using FluentData;
using System.Dynamic;
using System.Data;

namespace Eleanor.DAL
{
    public class SubscriberDAL : DALBase
    {
        public int Insert(dynamic subscriber)
        {
            IStoredProcedureBuilder command = DefaultContext.StoredProcedure("SubscriberInsert");
            command
                            .Parameter("Name", subscriber.Name as string)
                            .Parameter("Email", subscriber.Email as string)
                            .Parameter("Telephone", subscriber.Telephone as string)
                            .Parameter("CreateDate", (DateTime)subscriber.CreateDate)
                            .ParameterOut("SubscriberId", DataTypes.Int32, 4);
            command.Execute();
            return command.ParameterValue<int>("SubscriberId");
        }

        public int Update(dynamic subscriber)
        {
            IStoredProcedureBuilder command = DefaultContext.StoredProcedure("SubscriberUpdate");
            command
                            .Parameter("Name", subscriber.Name as string)
                            .Parameter("Email", subscriber.Email as string)
                            .Parameter("Telephone", subscriber.Telephone as string)
                            .Parameter("UpdateDate", (DateTime)subscriber.UpdateDate)
                            .Parameter("SubscriberId", subscriber.SubscriberId);
            return command.Execute();
        }

        public dynamic Select(string email)
        {
            const string sql = @"select * from subscriber where email=@0";
            return CustomMapperUsingDynamic(DefaultContext.Sql(sql).Parameters(email).QuerySingle<dynamic>());
        }

        public DataTable SelectTable()
        {
            var command = DefaultContext.Sql("select name, email, telephone from subscriber");
            return command.QuerySingle<DataTable>();
        }

        public List<dynamic> SelectAll()
        {
            var command = DefaultContext
                        .Sql("SubscriberSelectALL")
                        .CommandType(DbCommandTypes.StoredProcedure);
            return command.QueryMany<dynamic>();
        }

        public int Delete(int subscriberId)
        {
            var command = DefaultContext
                        .StoredProcedure("SubscriberDelete")
                        .Parameter("SubscriberId", subscriberId);
            return command.Execute();
        }

        public dynamic CustomMapperUsingDynamic(dynamic row)
        {
            if (row == null)
            {
                return null;
            }
            dynamic subscriber = new ExpandoObject();
            subscriber.SubscriberId = row.subscriber_id;
            subscriber.Name = row.name;
            subscriber.Email = row.email;
            subscriber.Telephone = row.telephone;
            subscriber.CreateDate = row.create_date;
            subscriber.UpdateDate = row.update_date;
            return subscriber;
        }   
    }
}